
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, 2012 and Azure
-- --------------------------------------------------
-- Date Created: 11/09/2015 00:00:54
-- Generated from EDMX file: E:\DevApp\ASP\ContosoUniversity\ContosoUniversity\DB\SchoolContext.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [School];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_CourseEnrollment]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Enrollment] DROP CONSTRAINT [FK_CourseEnrollment];
GO
IF OBJECT_ID(N'[dbo].[FK_StudentEnrollment]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Enrollment] DROP CONSTRAINT [FK_StudentEnrollment];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Course]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Course];
GO
IF OBJECT_ID(N'[dbo].[Student]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Student];
GO
IF OBJECT_ID(N'[dbo].[Enrollment]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Enrollment];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Course'
CREATE TABLE [dbo].[Course] (
    [CourseID] int IDENTITY(1,1) NOT NULL,
    [Title] nvarchar(50)  NOT NULL,
    [Credits] int  NOT NULL
);
GO

-- Creating table 'Student'
CREATE TABLE [dbo].[Student] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [LastName] nvarchar(50)  NOT NULL,
    [FirstMidName] nvarchar(50)  NOT NULL,
    [EnrollmentDate] datetime  NOT NULL
);
GO

-- Creating table 'Enrollment'
CREATE TABLE [dbo].[Enrollment] (
    [EnrollmentID] int IDENTITY(1,1) NOT NULL,
    [CourseID] int  NOT NULL,
    [StudentID] int  NOT NULL,
    [Grade] varchar(1)  NOT NULL,
    [Course_CourseID] int  NOT NULL,
    [Student_ID] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [CourseID] in table 'Course'
ALTER TABLE [dbo].[Course]
ADD CONSTRAINT [PK_Course]
    PRIMARY KEY CLUSTERED ([CourseID] ASC);
GO

-- Creating primary key on [ID] in table 'Student'
ALTER TABLE [dbo].[Student]
ADD CONSTRAINT [PK_Student]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [EnrollmentID] in table 'Enrollment'
ALTER TABLE [dbo].[Enrollment]
ADD CONSTRAINT [PK_Enrollment]
    PRIMARY KEY CLUSTERED ([EnrollmentID] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [Course_CourseID] in table 'Enrollment'
ALTER TABLE [dbo].[Enrollment]
ADD CONSTRAINT [FK_CourseEnrollment]
    FOREIGN KEY ([Course_CourseID])
    REFERENCES [dbo].[Course]
        ([CourseID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_CourseEnrollment'
CREATE INDEX [IX_FK_CourseEnrollment]
ON [dbo].[Enrollment]
    ([Course_CourseID]);
GO

-- Creating foreign key on [Student_ID] in table 'Enrollment'
ALTER TABLE [dbo].[Enrollment]
ADD CONSTRAINT [FK_StudentEnrollment]
    FOREIGN KEY ([Student_ID])
    REFERENCES [dbo].[Student]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_StudentEnrollment'
CREATE INDEX [IX_FK_StudentEnrollment]
ON [dbo].[Enrollment]
    ([Student_ID]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------